﻿/***************************************************************************
Author: Armin Schmidt; based on code by Florence Tan (CSIRO). No change to logic,
but updating matviews_vvg, instead of matviews.
Description: DB Scripts for creating functions to create materialised views.
Date: 23/04/2012
Version: 1.0
Version History:
	1.0	23/04/2012	Start of adapted script.
***************************************************************************/

/***************************************************************************
Drop the functions if they already exist.
WARNING: This will affect the functions if they were created from a SISS build.
At the moment these functions are the same. But maybe it is better to only 
define them if they don't already exist and use whatever is already there.
***************************************************************************/
DROP FUNCTION create_matview();
DROP FUNCTION drop_matview();
DROP FUNCTION refresh_matview();

/***************************************************************************
Function: create_matview 
***************************************************************************/
CREATE OR REPLACE FUNCTION create_matview(NAME, NAME)
RETURNS VOID
SECURITY DEFINER
LANGUAGE plpgsql AS '
DECLARE
	matview ALIAS FOR $1;
	view_name ALIAS FOR $2;
	entry matviews_vvg%ROWTYPE;
BEGIN
	SELECT * INTO entry FROM matviews_vvg WHERE mv_name = matview;

	IF FOUND THEN
		RAISE EXCEPTION ''Materialised view ''''%'''' already exists.'',
			matview;
	END IF;

	EXECUTE ''REVOKE ALL ON '' || view_name || '' FROM PUBLIC''; 

	EXECUTE ''GRANT SELECT ON '' || view_name || '' TO PUBLIC'';

	EXECUTE ''CREATE TABLE '' || matview || '' AS SELECT * FROM '' || view_name;

	EXECUTE ''REVOKE ALL ON '' || matview || '' FROM PUBLIC'';

	EXECUTE ''GRANT SELECT ON '' || matview || '' TO PUBLIC'';

	INSERT INTO matviews_vvg (mv_name, v_name, last_refresh)
	VALUES (matview, view_name, CURRENT_TIMESTAMP); 

	RETURN;
	END
';

/***************************************************************************
Function: drop_matview 
***************************************************************************/
CREATE OR REPLACE FUNCTION drop_matview(NAME) RETURNS VOID
SECURITY DEFINER
LANGUAGE plpgsql AS '
DECLARE
	matview ALIAS FOR $1;
	entry matviews_vvg%ROWTYPE;
BEGIN
	SELECT * INTO entry FROM matviews_vvg WHERE mv_name = matview;

	IF NOT FOUND THEN
		RAISE EXCEPTION ''Materialised view % does not exist.'', matview;
	END IF;

	EXECUTE ''DROP TABLE '' || matview;
	DELETE FROM matviews_vvg WHERE mv_name=matview;

	RETURN;
END
';

/***************************************************************************
Function: refresh_matview Function
***************************************************************************/
CREATE OR REPLACE FUNCTION refresh_matview(name) RETURNS VOID
SECURITY DEFINER
LANGUAGE plpgsql AS '
DECLARE 
	matview ALIAS FOR $1;
	entry matviews_vvg%ROWTYPE;
BEGIN
	SELECT * INTO entry FROM matviews_vvg WHERE mv_name = matview;

	IF NOT FOUND THEN
		RAISE EXCEPTION ''Materialised view % does not exist.'', matview;
	END IF;

	EXECUTE ''DELETE FROM '' || matview;
	EXECUTE ''INSERT INTO '' || matview || '' SELECT * FROM '' || entry.v_name;

	UPDATE matviews_vvg
	SET last_refresh=CURRENT_TIMESTAMP
	WHERE mv_name=matview;

	RETURN;
END
';
